using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Xml;


namespace Index_Management
{
    public partial class Form3 : Form
    {
        public Server _dataSource;
        public Database _database;
        public string[] _tableList;
        public int _indexCount;
        public int _fKeyCount;
        public int _triggerCount;
        public Form5 DisplaySummary;
        public XmlDocument _objectList;
        string _dropIndexScript;
        string _createIndexScript;
        string _dropFKeyScript;
        string _createFKeyScript;
        string _dropTriggerScript;
        string _createTriggerScript;

        public Form3()
        {
            InitializeComponent();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            checkedListBox_Tables.Enabled = false;
            DisplaySummary = new Form5();
            button_Next.Enabled = false;
        }

        private void SetConnection(object sender, EventArgs e)
        {
            //Display the connection manager dialog and fetch the Data Source and Database values.
            Form2 getConnectionInfo = new Form2();
            getConnectionInfo.ShowDialog();
            //getConnectionInfo.Conn is set to true if the connection manager successfully connects to a database.
            if (getConnectionInfo.Conn) 
            {
                checkedListBox_Tables.Items.Clear();
                textBox_ConnString.Text = getConnectionInfo.DataSource.ConnectionContext.ConnectionString;
                _dataSource = getConnectionInfo.getDataSource();
                _database = getConnectionInfo.getDB();
                checkedListBox_Tables.Enabled = true;
                groupBox2.Enabled = true;
                //Load the list of tables in the database.
                foreach (Table tab in _database.Tables)
                {
                    checkedListBox_Tables.Items.Add(tab.Schema + "." + tab.Name);
                }
            }
            getConnectionInfo.Close();
            getConnectionInfo.Dispose();
        }

        private void SelectAll(object sender, EventArgs e)
        {
            //Check all the items in the table list box
            for (int item = 0; item < checkedListBox_Tables.Items.Count; item++)
            {
                checkedListBox_Tables.SetItemChecked(item, true);
            }
        }

        private void InvertSelection(object sender, EventArgs e)
        {
            //Invert the selection on the items in the table list box
            for (int item = 0; item < checkedListBox_Tables.Items.Count; item++)
            {
                if (checkedListBox_Tables.GetItemChecked(item))
                {
                    checkedListBox_Tables.SetItemChecked(item, false);
                }
                else
                {
                    checkedListBox_Tables.SetItemChecked(item, true);
                }
            }
        }

        private void Cancel(object sender, EventArgs e)
        {
            DialogResult cancelMessage = MessageBox.Show("Are you sure you want to exit the wizard?", "Cancel wizard.", MessageBoxButtons.YesNo);
            if (cancelMessage == DialogResult.Yes)
            {
                Application.Exit();
            }
        }

        private void button_Next_Click(object sender, EventArgs e)
        {
            DisplaySummary.DisplayCountSummary(_indexCount, _fKeyCount, _triggerCount);
            DisplaySummary.SetScript(textBox1.Text);
            this.Hide();
            DisplaySummary.ShowDialog();
            //this.Dispose();
        }

        private void GetListOfCheckedTables()
        {
            _tableList = new string[checkedListBox_Tables.CheckedItems.Count];
            int count = 0;
            foreach (object CheckedItem in checkedListBox_Tables.CheckedItems)
            {
                _tableList[count++] = CheckedItem.ToString();
            }
        }

        private void GetObjectCounts()
        {
            //Fetch the number of entities in the selected tables. This is used for displaying the script status.
            foreach (Table tab in _database.Tables)
            {
                foreach (string TableName in _tableList)
                {
                    if (tab.Schema + "." + tab.Name == TableName)
                    {
                        foreach (Index indx in tab.Indexes)
                        {
                            if (checkBox_AllIndexes.Checked)
                            {
                                _indexCount++;
                            }
                            else if (checkBox_ClusteredIndexes.Checked)
                            {
                                if (indx.IsClustered)
                                {
                                    _indexCount++;
                                }
                            }
                            else if (checkBox_NonClusteredIndexes.Checked)
                            {
                                if (!indx.IsClustered)
                                {
                                    _indexCount++;
                                }
                            }
                        }

                        foreach (ForeignKey FK in tab.ForeignKeys)
                        {
                            _fKeyCount++;
                        }
                        foreach (Trigger trig in tab.Triggers)
                        {
                            _triggerCount++;
                        }
                    }
                }
            }
        }

        private void Script(object sender, EventArgs e)
        {
            button_ViewScript.Enabled = false;
            GetListOfCheckedTables();
            _indexCount = 0;
            _fKeyCount = 0;
            _triggerCount = 0;
            GetObjectCounts();
            textBox1.Text = "";
            label_IndexStatus.Visible = true;
            label_IndexStatus.Refresh();
            label_FKStatus.Visible = true;
            label_FKStatus.Refresh();
            label_TriggersStatus.Visible = true;
            label_TriggersStatus.Refresh();
            label_IndexValue.Text = "";
            label_FKValue.Text = "";
            label_TriggerValue.Text = "";
            label_IndexValue.Visible = true;
            label_FKValue.Visible = true;
            label_TriggerValue.Visible = true;
            _dropIndexScript="";
            _dropFKeyScript="";
            _dropTriggerScript = "";
            _createIndexScript = "";
            _createFKeyScript = "";
            _createTriggerScript = "";
            int _incIndexCount = 1;
            int _incFKeyCount = 1; 
            int _incTriggerCount = 1;
            foreach (Table tab in _database.Tables)
            {
                foreach (string tableName in _tableList)
                {
                    if (tab.Schema + "." + tab.Name == tableName)
                    {
                        DisplaySummary.AddNodeToTree(tab.Schema + "." + tab.Name,"");
                        DisplaySummary.AddNodeToTree("Indexes", tab.Schema + "." + tab.Name);
                        DisplaySummary.AddNodeToTree("Foreign Keys", tab.Schema + "." + tab.Name);
                        DisplaySummary.AddNodeToTree("Triggers", tab.Schema + "." + tab.Name);                     
                        if (checkBox_AllIndexes.Checked)
                        {
                            ScriptIndexes("All", tab, ref _incIndexCount);
                        }
                        else if (checkBox_ClusteredIndexes.Checked)
                        {
                            ScriptIndexes("Clustered", tab, ref _incIndexCount);
                        }
                        else if (checkBox_NonClusteredIndexes.Checked)
                        {
                            ScriptIndexes("Non-Clustered", tab, ref _incIndexCount);
                        }

                        if (checkBox_FK.Checked)
                        {
                            ScriptFK(tab, ref _incFKeyCount);
                        }
                        if (checkBox_Triggers.Checked)
                        {
                            ScriptTriggers(tab,ref _incTriggerCount);
                        }
                    }
                }
            }
            MessageBox.Show("Scripting complete.");
            button_ViewScript.Enabled = true;
            
            //SHOW SCRIPT
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-------INDEXES--------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-----DROP SCRIPTS-----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_dropIndexScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----CREATE SCRIPTS----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_createIndexScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);

            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-----FOREIGN KEYS-----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-----DROP SCRIPTS-----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_dropFKeyScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----CREATE SCRIPTS----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_createFKeyScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);

            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-------TRIGGERS-------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("-----DROP SCRIPTS-----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_dropTriggerScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----CREATE SCRIPTS----");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText("----------------------");
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(_createTriggerScript);
            textBox1.AppendText(Environment.NewLine);
            textBox1.AppendText(Environment.NewLine);

            button_Next.Enabled = true;
        }
        
        public void ScriptIndexes(string typeOfIndex,Table tab,ref int count)
        {
            ScriptingOptions so = new ScriptingOptions();
            StringCollection sc;
            sc = new StringCollection();
            foreach (Index index in tab.Indexes)
            {
                if (checkBox_AllIndexes.Checked)
                {
                    DisplaySummary.AddNodeToTree(index.Name, "Indexes", tab.Schema + "." + tab.Name);
                    //DISPLAY SCRIPTING STATUS
                    label_IndexValue.Text = count.ToString() + " of " + _indexCount.ToString();
                    label_IndexValue.Refresh();
                    count++;

                    //DROP SCRIPTS
                    so.ScriptDrops = true;
                    so.IncludeIfNotExists = true;
                    sc = index.Script(so);
                    foreach (string script in sc)
                    {
                        _dropIndexScript = _dropIndexScript + script;
                        _dropIndexScript = _dropIndexScript + Environment.NewLine;
                    }
                    _dropIndexScript = _dropIndexScript + Environment.NewLine;

                    //CREATE SCRIPTS
                    so.ScriptDrops = false;
                    so.IncludeIfNotExists = true;
                    sc = index.Script(so);
                    foreach (string script in sc)
                    {
                        _createIndexScript = _createIndexScript + script;
                        _createIndexScript = _createIndexScript + Environment.NewLine;
                    }
                    _createIndexScript = _createIndexScript + Environment.NewLine;
                }
                else if (checkBox_ClusteredIndexes.Checked)
                {
                    if (index.IsClustered)
                    {
                        DisplaySummary.AddNodeToTree(index.Name, "Indexes", tab.Schema + "." + tab.Name);
                        //DISPLAY SCRIPTING STATUS
                        label_IndexValue.Text = count.ToString() + " of " + _indexCount.ToString();
                        label_IndexValue.Refresh();
                        count++;

                        //DROP SCRIPTS
                        so.ScriptDrops = true;
                        so.IncludeIfNotExists = true;
                        sc = index.Script(so);
                        foreach (string script in sc)
                        {
                            _dropIndexScript = _dropIndexScript + script;
                            _dropIndexScript = _dropIndexScript + Environment.NewLine;
                        }
                        _dropIndexScript = _dropIndexScript + Environment.NewLine;

                        //CREATE SCRIPTS
                        so.ScriptDrops = false;
                        so.IncludeIfNotExists = true;
                        sc = index.Script(so);
                        foreach (string script in sc)
                        {
                            _createIndexScript = _createIndexScript + script;
                            _createIndexScript = _createIndexScript + Environment.NewLine;
                        }
                        _createIndexScript = _createIndexScript + Environment.NewLine;
                    }
                }
                else if (checkBox_NonClusteredIndexes.Checked)
                {
                    if (!index.IsClustered)
                    {
                        DisplaySummary.AddNodeToTree(index.Name, "Indexes", tab.Schema + "." + tab.Name);
                        //DISPLAY SCRIPTING STATUS
                        label_IndexValue.Text = count.ToString() + " of " + _indexCount.ToString();
                        label_IndexValue.Refresh();
                        count++;

                        //DROP SCRIPTS
                        so.ScriptDrops = true;
                        so.IncludeIfNotExists = true;
                        sc = index.Script(so);
                        foreach (string script in sc)
                        {
                            _dropIndexScript = _dropIndexScript + script;
                            _dropIndexScript = _dropIndexScript + Environment.NewLine;
                        }
                        _dropIndexScript = _dropIndexScript + Environment.NewLine;


                        //CREATE SCRIPTS
                        so.ScriptDrops = false;
                        so.IncludeIfNotExists = true;
                        sc = index.Script(so);
                        foreach (string script in sc)
                        {
                            _createIndexScript = _createIndexScript + script;
                            _createIndexScript = _createIndexScript + Environment.NewLine;
                        }
                        _createIndexScript = _createIndexScript + Environment.NewLine;
                    }
                }
            }
        }

        public void ScriptFK(Table tab,ref int count)
        {
            ScriptingOptions so = new ScriptingOptions();
            StringCollection sc;
            sc = new StringCollection();
            foreach (ForeignKey fKey in tab.ForeignKeys)
            {
                DisplaySummary.AddNodeToTree(fKey.Name, "Foreign Keys", tab.Schema + "." + tab.Name);
                //DISPLAY SCRIPTING STATUS
                label_FKValue.Text = count.ToString() + " of " + _fKeyCount.ToString();
                label_FKValue.Refresh();
                count++;

                //DROP SCRIPTS
                so.ScriptDrops = true;
                so.IncludeIfNotExists = true;
                sc = fKey.Script(so);
                foreach (string script in sc)
                {
                    _dropFKeyScript = _dropFKeyScript + script;
                    _dropFKeyScript = _dropFKeyScript + Environment.NewLine;
                }
                _dropFKeyScript = _dropFKeyScript + Environment.NewLine;
                
                //CREATE SCRIPTS
                so.ScriptDrops = false;
                so.IncludeIfNotExists = true;
                so.DriForeignKeys = true;
                sc = fKey.Script(so);
                //sc = fKey.Script();
                foreach (string script in sc)
                {
                    _createFKeyScript = _createFKeyScript + script;
                    _createFKeyScript = _createFKeyScript + Environment.NewLine;
                }
                _createFKeyScript = _createFKeyScript + Environment.NewLine;
            }
        }

        public void ScriptTriggers(Table tab,ref int count)
        {
            ScriptingOptions so = new ScriptingOptions();
            StringCollection sc;
            sc = new StringCollection();
            foreach (Trigger trigger in tab.Triggers)
            {
                DisplaySummary.AddNodeToTree(trigger.Name, "Triggers", tab.Schema + "." + tab.Name);
                //DISPLAY SCRIPTING STATUS
                label_TriggerValue.Text = count.ToString() + " of " + _triggerCount.ToString();
                label_TriggerValue.Refresh();
                count++;

                //DROP SCRIPTS
                so.ScriptDrops = true;
                so.IncludeIfNotExists = true;
                sc = trigger.Script(so);
                foreach (string script in sc)
                {
                    _dropTriggerScript = _dropTriggerScript + script;
                }
                _dropTriggerScript = _dropTriggerScript + Environment.NewLine;

                //CREATE SCRIPTS
                so.ScriptDrops = false;
                so.IncludeIfNotExists = true;
                sc = trigger.Script(so);
                //sc = fKey.Script();
                foreach (string script in sc)
                {
                    _createTriggerScript = _createTriggerScript + script;
                    _createTriggerScript = _createTriggerScript + Environment.NewLine;
                }
                _createTriggerScript = _createTriggerScript + Environment.NewLine;
            }
        }

        private void button_ViewScript_Click(object sender, EventArgs e)
        {
            Form4 ScriptPage = new Form4(textBox1.Text);
            ScriptPage.ShowDialog();
        }

        private void ChooseAllIndexes(object sender, EventArgs e)
        {
            //Toggle Index check boxes 
            if (checkBox_AllIndexes.Checked)
            {
                checkBox_ClusteredIndexes.Checked = true;
                checkBox_NonClusteredIndexes.Checked = true;
            }
            else
            {
                checkBox_ClusteredIndexes.Checked = false;
                checkBox_NonClusteredIndexes.Checked = false;
            }
        }

        private void checkBox_ClusteredIndexes_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox_ClusteredIndexes.Checked && checkBox_NonClusteredIndexes.Checked)
            {
                checkBox_AllIndexes.Checked=true;
            }
            else
            {
                checkBox_AllIndexes.Checked=false;
            }
        }

        private void checkBox_NonClusteredIndexes_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox_ClusteredIndexes.Checked && checkBox_NonClusteredIndexes.Checked)
            {
                checkBox_AllIndexes.Checked = true;
            }
            else
            {
                checkBox_AllIndexes.Checked = false;
            }
        }

    }
}
